﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class RepaymentHandler
    {
        public static int AddRepayment(string Repayment, string Freq, string Method1, string NextPaymentDate, string TotalPaid, string ContractualMaturity, string ProjectedMaturity, string Residual, string Balance, string DailyRate, string LoanID)
        {
            int RepaymentID = 0;
            try
            {
                if (NextPaymentDate == "")
                    NextPaymentDate = null;
                if (ContractualMaturity == "")
                    ContractualMaturity = null;
                if (ProjectedMaturity == "")
                    ProjectedMaturity = null;

                var para = new[] {
                DBhelper.SetParameter("@Repayment", Repayment),
                DBhelper.SetParameter("@Freq", Freq),
                DBhelper.SetParameter("@Method", Method1),
                DBhelper.SetParameter("@NextPaymentDate", NextPaymentDate),
                DBhelper.SetParameter("@TotalPaid", TotalPaid),
                DBhelper.SetParameter("@ContractualMaturity", ContractualMaturity),
                DBhelper.SetParameter("@ProjectedMaturity", ProjectedMaturity),
                DBhelper.SetParameter("@Residual", Residual),
                DBhelper.SetParameter("@Balance", Balance),
                DBhelper.SetParameter("@DailyRate", DailyRate),
                DBhelper.SetParameter("@LoanID", LoanID)
                };

                string sqlInertRepaymentQuety = "insert into am_repayments(Repayment, Freq, Method, NextPaymentDate, TotalPaid, ContractualMaturity, ProjectedMaturity, Residual, Balance, DailyRate, LoanID) values(@Repayment,@Freq,@Method,STR_TO_DATE(@NextPaymentDate,'%m/%d/%Y'),@TotalPaid,STR_TO_DATE(@ContractualMaturity,'%m/%d/%Y'),STR_TO_DATE(@ProjectedMaturity,'%m/%d/%Y'),@Residual,@Balance,@DailyRate,@LoanID);SELECT RepaymentID FROM `am_repayments` order by  RepaymentID desc limit 1";
                DataSet dtdata1 = DBhelper.ExecuteDataset(sqlInertRepaymentQuety, para);
                DataTable dt = dtdata1.Tables[0];
                if (dt.Rows.Count > 0)
                    RepaymentID = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return RepaymentID;
        }

        public static int UpdateRepayment(string Repayment, string Freq, string Method1, string NextPaymentDate, string TotalPaid, string ContractualMaturity, string ProjectedMaturity, string Residual, string Balance, string DailyRate, string LoanID)
        {
            int retvalu = 0;
            try
            {
                if (NextPaymentDate == "")
                    NextPaymentDate = null;
                if (ContractualMaturity == "")
                    ContractualMaturity = null;
                if (ProjectedMaturity == "")
                    ProjectedMaturity = null;

                var para = new[] {
                DBhelper.SetParameter("@Repayment", Repayment),
                DBhelper.SetParameter("@Freq", Freq),
                DBhelper.SetParameter("@Method", Method1),
                DBhelper.SetParameter("@NextPaymentDate", NextPaymentDate),
                DBhelper.SetParameter("@TotalPaid", TotalPaid),
                DBhelper.SetParameter("@ContractualMaturity", ContractualMaturity),
                DBhelper.SetParameter("@ProjectedMaturity", ProjectedMaturity),
                DBhelper.SetParameter("@Residual", Residual),
                DBhelper.SetParameter("@Balance", Balance),
                DBhelper.SetParameter("@DailyRate", DailyRate),
                DBhelper.SetParameter("@LoanID", LoanID)
                };

                string sqlUpdateRepaymentQuety = "update am_repayments set Repayment=@Repayment,Freq=@Freq,Method=@Method,NextPaymentDate=STR_TO_DATE(@NextPaymentDate,'%m/%d/%Y'),TotalPaid=@TotalPaid,ContractualMaturity=STR_TO_DATE(@ContractualMaturity,'%m/%d/%Y'),ProjectedMaturity=STR_TO_DATE(@ProjectedMaturity,'%m/%d/%Y'),Residual=@Residual,Balance=@Balance,DailyRate=@DailyRate where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateRepaymentQuety, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int AddArrears(string NumofDishonours, string TotalArrears, string DaysOverdue, string DateofDefault, string LoanID)
        {
            int ArrearsID = 0;
            try
            {
                if (DateofDefault == "")
                    DateofDefault = null;

                var para = new[] {
                DBhelper.SetParameter("@NumofDishonours", NumofDishonours),
                DBhelper.SetParameter("@TotalArrears", TotalArrears),
                DBhelper.SetParameter("@DaysOverdue", DaysOverdue),
                DBhelper.SetParameter("@DateofDefault", DateofDefault),
                DBhelper.SetParameter("@LoanID", LoanID),
                };

                string sqlInertArrearsQuety = "insert into am_arrears(TotalArrears, DaysOverdue, DateofDefault, NumofDishonours, LoanID) values(@TotalArrears,@DaysOverdue,STR_TO_DATE(@DateofDefault,'%m/%d/%Y'),@NumofDishonours,@LoanID);SELECT ArrearsID FROM `am_arrears` order by  ArrearsID desc limit 1";
                DataSet dtdata1 = DBhelper.ExecuteDataset(sqlInertArrearsQuety, para);
                DataTable dt = dtdata1.Tables[0];
                if (dt.Rows.Count > 0)
                    ArrearsID = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ArrearsID;
        }

        public static int UpdateArrears(string NumofDishonours, string TotalArrears, string DaysOverdue, string DateofDefault, string LoanID)
        {
            int retvalu = 0;
            try
            {
                if (DateofDefault == "")
                    DateofDefault = null;

                var para = new[] {
                DBhelper.SetParameter("@NumofDishonours", NumofDishonours),
                DBhelper.SetParameter("@TotalArrears", TotalArrears),
                DBhelper.SetParameter("@DaysOverdue", DaysOverdue),
                DBhelper.SetParameter("@DateofDefault", DateofDefault),
                DBhelper.SetParameter("@LoanID", LoanID),
                };

                string sqlUpdateArrearsQuety = "update am_arrears set TotalArrears=@TotalArrears, DaysOverdue=@DaysOverdue, DateofDefault=STR_TO_DATE(@DateofDefault,'%m/%d/%Y'), NumofDishonours=@NumofDishonours where LoanID=@LoanID";
                DBhelper.ExecuteNonQuery(sqlUpdateArrearsQuety, para);
                retvalu = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static int AddBankInfo(string BankName, string AccountNo, string BSB, int LoanID, string LoanType, string AccountHolder)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BankName", BankName),
                    DBhelper.SetParameter("@AccountNo", AccountNo),
                    DBhelper.SetParameter("@BSB", BSB),
                    DBhelper.SetParameter("@LoanID", LoanID), 
                    DBhelper.SetParameter("@LoanType", LoanType),
                    DBhelper.SetParameter("@AccountHolder", AccountHolder)  
                };

                string str = "insert into am_bankdetails(bankname,branchname,bsb,accname,accountno,SBI,LoanID,LoanType,HolderName) values(@BankName,null,@BSB,null,@AccountNo,null,@LoanID,@LoanType,@AccountHolder)";
                DBhelper.ExecuteNonQuery(str, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int UpdateBankInfo(string BankName, string AccountNo, string BSB, int LoanID, string LoanType, string AccountHolder)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@BankName", BankName),
                    DBhelper.SetParameter("@AccountNo", AccountNo),
                    DBhelper.SetParameter("@BSB", BSB),
                    DBhelper.SetParameter("@LoanID", LoanID),
                    DBhelper.SetParameter("@AccountHolder", AccountHolder),
                    DBhelper.SetParameter("@LoanType", LoanType)
                };

                string query = "select bankid from am_bankdetails where LoanID=@LoanID";
                DataSet ds = DBhelper.ExecuteDataset(query, para);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    query = "update am_bankdetails set bankname=@BankName,bsb=@BSB,accountno=@AccountNo,HolderName=@AccountHolder where LoanID=@LoanID and LoanType=@LoanType";
                    DBhelper.ExecuteNonQuery(query, para);
                    intReturnValue = 1;
                }
                else
                {
                    query = "insert into am_bankdetails(bankname,branchname,bsb,accname,accountno,SBI,LoanID,LoanType,HolderName) values(@BankName,null,@BSB,null,@AccountNo,null,@LoanID,@LoanType,@AccountHolder)";
                    DBhelper.ExecuteNonQuery(query, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static DataTable SearchRepaymentByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                dt = DBhelper.ExecuteDataset("select * from am_repayments where loanid=@LoanID", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchArrearsByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                dt = DBhelper.ExecuteDataset("select * from am_arrears where loanid=@LoanID", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable SearchBankDetailByLoanID(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                dt = DBhelper.ExecuteDataset("select * from am_bankdetails where loanid=@LoanID", para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static int AddPaymentSchedule(string PaymentSchedule, string Amount, string LoanID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentSchedule", PaymentSchedule),
                    DBhelper.SetParameter("@Amount", Amount),
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                string sbSql = "Select * From am_paymentschedule Where date = @PaymentSchedule";
                DataSet ds = DBhelper.ExecuteDataset(sbSql, para);

                if (ds.Tables[0].Rows.Count > 0)
                    intReturnValue = 0;
                else
                {
                    sbSql = "insert into am_paymentschedule(date,amount,LoanID) values(STR_TO_DATE(@PaymentSchedule,'%m/%d/%Y'),@Amount,@LoanID)";
                    DBhelper.ExecuteNonQuery(sbSql, para);
                    intReturnValue = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int EditPaymentSchedule(int paymentscheduleid, string PaymentSchedule, string Amount, string Balance)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentscheduleId", paymentscheduleid),
                    DBhelper.SetParameter("@PaymentSchedule", PaymentSchedule),
                    DBhelper.SetParameter("@Amount", Amount),
                    DBhelper.SetParameter("@Balance", Balance)
                };

                string sbSql = "update am_repaymentschedule set date = STR_TO_DATE(@PaymentSchedule,'%m/%d/%Y'), amount=@Amount,balance=@Balance where paymentscheduleid = @PaymentscheduleId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool DeletePaymentSchedule(int paymentscheduleid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@PaymentscheduleId", paymentscheduleid),
                };

                DBhelper.ExecuteNonQuery("delete from am_paymentschedule where paymentscheduleid=@PaymentscheduleId", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static DataTable DisplayPaymentSchedule(int LoanID)
        {
            DataTable table = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID)
                };

                string sqlqry = "select date,CAST(DAYNAME(date) AS CHAR) as day,amount from am_repaymentschedule where loanid=@LoanID order by date";
                table = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return table;
        }
    }
}
